package com.edu.hre.db.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.log4j.Logger;



import com.edu.hre.db.config.BaseDAO;
import com.edu.hre.db.config.DBPool;
import com.edu.hre.db.dto.HreEmpBasicsalary;
import com.edu.hre.db.dto.HreEmpEducation;
import com.edu.hre.db.dto.HreEmpEmergencyContact;
import com.edu.hre.db.dto.HreEmpDependent;
import com.edu.hre.db.dto.HreEmpWorkExperience;


public class EmpEducationDAO  extends BaseDAO {
	private static final Logger logger = Logger.getLogger(EmpEducationDAO.class);
	@SuppressWarnings("deprecation")
	public List<HreEmpEducation> getEducationByEmployeeId(
			int employeeNumber) {
		List<HreEmpEducation> listEducation = new ArrayList<HreEmpEducation>();
		Connection conn = null;
		PreparedStatement preStmt = null;
		ResultSet rs = null;
		try {
			conn = DBPool.getConnection();
			String strSQLQuery = "SELECT ee.*,e.name AS level,m.name AS full_major FROM hre_emp_education ee "+
			"JOIN hre_education e ON ee.education_id = e.id "+
			"JOIN hre_major m ON ee.major = m.id "+
			"WHERE ee.emp_number = "
					+ employeeNumber;
			preStmt = conn.prepareStatement(strSQLQuery);
			rs = preStmt.executeQuery();
			while (rs.next()) {
				HreEmpEducation education = new HreEmpEducation();
				education.setInstitute(rs.getString("institute"));
				education.setLevel(rs.getString("level"));
				education.setFullMajor(rs.getString("full_major"));
				education.setYear(rs.getShort("year"));
				education.setScore(rs.getString("score"));
				Date d = null;
				if(rs.getDate("start_date") != null){
					d= (rs.getDate("start_date"));
					d.setYear(d.getYear()-1900);
				}
				education.setStartDate(d);
				if(rs.getDate("end_date") != null){
					d= (rs.getDate("end_date"));
					d.setYear(d.getYear()-1900);
				}
				education.setEndDate(d);
				//.....
				//.....
				listEducation.add(education);
			}
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage());
		} finally {
			// đóng PreparedStatement, ResultSet và đẩy connection vào pool
			releaseConnection(conn, preStmt, rs);
		}
		return listEducation;
	}

}
